package com.qf.dao.impl;

import com.qf.dao.StudentDao;
import com.qf.pojo.Page;
import com.qf.pojo.Student;
import com.qf.utils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @date 2022/3/26
 * @desc
 */
public class StudentDaoImpl implements StudentDao {

    QueryRunner  queryRunner = new QueryRunner(DbUtils.getDs());


    @Override
    public List<Student> findStudents() {
        String sql = "select * from t_student";
        List<Student> students = null;
        try {
            students = queryRunner.query(sql, new BeanListHandler<Student>(Student.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return students;
    }

    @Override
    public int deleteStudentById(int id) {
        String sql="delete from t_student where id = ?";
        int update = 0;
        try {
            update = queryRunner.update(sql, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return update;
    }

    @Override
    public int addStudent(Student student) {
        String sql = "insert into t_student (id,name,sex,birthday,phone,address,hobby) values (null,?,?,?,?,?,?)";

        int update = 0;
        try {
            update = queryRunner.update(sql, student.getName(), student.getSex(), student.getBirthday(), student.getPhone(), student.getAddress(), student.getHobby());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return update;
    }

    @Override
    public Student findStudentById(int id) {
        String sql = "select * from t_student where id = ?";
        Student student = null;
        try {
            student = queryRunner.query(sql, new BeanHandler<>(Student.class), id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return student;
    }

    @Override
    public int updateStudentById(Student student) {
        String sql="update t_student set name = ? ,sex=? , birthday =? ,phone =? ,address = ? ,hobby=? where id = ?";

        int update = 0;
        try {
            update = queryRunner.update(sql, student.getName(), student.getSex(), student.getBirthday(), student.getPhone(), student.getAddress(), student.getHobby(), student.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return update;
    }

    @Override
    public List<Student> getPageDate(Page page) {
        //查询分页列表数据
        String sql="select * from t_student limit ?,?";

        List<Student> data = null;
        try {
            data = queryRunner.query(sql, new BeanListHandler<>(Student.class), (page.getPageNum()-1) * page.getPageSize(), page.getPageSize());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return data;
    }

    @Override
    public int getTotalRecored() {
        //查询总记录数
        String sql = "select count(*) from t_student";
        try {
            Long count = queryRunner.query(sql, new ScalarHandler<>());
            return count.intValue();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    @Override
    public List<Student> getPageDataByCondition(Page page, Student student) {
        //查询分页列表数据
        StringBuffer sb = new StringBuffer("select * from t_student ");

        // 模糊查询两个条件 有可能传 有可能不传
        if(student!=null&&student.getName()!=null){
            sb.append(" and name like '%"+student.getName()+"%'");
        }

        if(student!=null&&student.getAddress()!=null){
            sb.append(" and address like '%"+student.getAddress()+"%'");
        }

        sb.append(" limit ?,?");

        //把sql 语句中的 第一个 and 替换成  where      刚刚好
        String sql=sb.toString().replaceFirst("and","where");

        System.out.println(sql);

        List<Student> data = null;
        try {
            data = queryRunner.query(sql, new BeanListHandler<>(Student.class), (page.getPageNum()-1) * page.getPageSize(), page.getPageSize());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return data;
    }

    @Override
    public int getTotalRecoreByCondition(Student student) {
        //查询总记录数

        StringBuffer sb = new StringBuffer("select count(*) from t_student ");

        if(student!=null&&student.getName()!=null){
            sb.append(" and name like '%"+student.getName()+"%'");
        }

        if(student!=null&&student.getAddress()!=null){
            sb.append(" and address like '%"+student.getAddress()+"%' ");
        }

        //把sql 语句中的 第一个 and 替换成  where      刚刚好
        String sql=sb.toString().replaceFirst("and","where");
        System.out.println(sql);

        try {
            Long count = queryRunner.query(sql, new ScalarHandler<>());
            return count.intValue();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }
}
